package com.sx.dao;

import com.sx.domain.Player;
import com.sx.utils.DBUtils;
import com.sx.utils.RandomValue;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class PlayerfoDAO {

  /**
   * 查
   */
  public static List<Player> findAll() throws SQLException {
    Connection conn = DBUtils.getConnection();
    QueryRunner runner = new QueryRunner();
    String sql = "select * from player";
    List<Player> playerList = DBUtils.select(Player.class, conn, sql);
    conn.close();
    return playerList;
  }


  public static List<Player> findByKey(String key) throws Exception {
    Connection conn = DBUtils.getConnection();
    String sql = "select * from game where " + key;//sql查询语句,查找是否有指定或学号与密码
    QueryRunner runner = new QueryRunner();
    List<Player> playerList = runner.query(conn, sql, new BeanListHandler<>(Player.class));
//    List<Player> playerList = DBUtils.select(Player.class, conn, sql);
    conn.close();//关闭连接
    return playerList;
  }

  /**
   * 插入
   */
  public static void add() throws SQLException {
    Connection conn = DBUtils.getConnection();
    QueryRunner runner = new QueryRunner();

    String sql = "insert ignore into player values(?,?,?,?,?,?)";
    runner.update(conn,sql,null,RandomValue.getChineseName(),RandomValue.getTel(),"尼克斯",String.valueOf(RandomValue.getNum(10,99)),RandomValue.getNum(20,25));
    conn.close();
  }

  public static void delPlayerById(String id) throws SQLException {
    Connection conn = DBUtils.getConnection();
    QueryRunner runner = new QueryRunner();
    String sql = "delete from player where id like "+ id ;
    runner.update(conn,sql);
    //runner.insert(conn,sql ,null,"佘欣3", "110", "lakers", "23", 20);
//    System.out.println(runner.update(conn,sql ,null ,"佘欣3", "110", "lakers", "23", 20));
    conn.close();
  }

  public static void add(String name, String category, String level) throws SQLException {
    Connection conn = DBUtils.getConnection();
    QueryRunner runner = new QueryRunner();
    String sql = "insert into player values(?,?,?,?,?,?)" ;
    runner.update(conn,sql,null,name,category,level);
    conn.close();
  }


  public static void update(String id, String age, String tel, String team, String team_id) throws SQLException {
    Connection conn = DBUtils.getConnection();
    QueryRunner runner = new QueryRunner();
    String sql = "update player set tel=?,team=?,team_id=?,age=? where id=? " ;
    runner.update(conn,sql,tel,team,team_id,age,id);
  }
}
